-- FlowPlan数据库初始化数据脚本
-- 插入测试和演示数据

-- 插入默认组织数据
INSERT INTO sys_org (org_name, org_code, parent_id) VALUES 
('技术部', 'TECH', NULL),
('产品部', 'PRODUCT', NULL),
('测试部', 'QA', NULL),
('运维部', 'OPS', NULL),
('设计部', 'DESIGN', NULL);

-- 插入子组织
INSERT INTO sys_org (org_name, org_code, parent_id) VALUES 
('前端组', 'TECH_FE', 1),
('后端组', 'TECH_BE', 1),
('移动端组', 'TECH_MOBILE', 1),
('产品规划组', 'PRODUCT_PLAN', 2),
('用户研究组', 'PRODUCT_UX', 2),
('功能测试组', 'QA_FUNC', 3),
('自动化测试组', 'QA_AUTO', 3);

-- 插入默认用户数据（密码统一为：flowplan123）
-- 密码哈希值：$2a$10$YourPasswordHashHereForflowplan123
INSERT INTO sys_user (username, email, password_hash, role, org_id, avatar_url, is_active) VALUES 
-- 管理员
('admin', 'admin@flowplan.com', '$2a$10$N.zmdr9k7uOCQb376NoUnuTJ8iAt6Z5EHsM8lE9P2kg9xjdNdCj0y', 'ADMIN', 1, NULL, true),

-- 开发人员
('zhang_san', 'zhangsan@flowplan.com', '$2a$10$N.zmdr9k7uOCQb376NoUnuTJ8iAt6Z5EHsM8lE9P2kg9xjdNdCj0y', 'DEVELOPER', 6, NULL, true),
('li_si', 'lisi@flowplan.com', '$2a$10$N.zmdr9k7uOCQb376NoUnuTJ8iAt6Z5EHsM8lE9P2kg9xjdNdCj0y', 'DEVELOPER', 7, NULL, true),
('wang_wu', 'wangwu@flowplan.com', '$2a$10$N.zmdr9k7uOCQb376NoUnuTJ8iAt6Z5EHsM8lE9P2kg9xjdNdCj0y', 'DEVELOPER', 8, NULL, true),

-- 需求方/产品经理
('product_manager', 'pm@flowplan.com', '$2a$10$N.zmdr9k7uOCQb376NoUnuTJ8iAt6Z5EHsM8lE9P2kg9xjdNdCj0y', 'DEMANDER', 9, NULL, true),
('business_analyst', 'ba@flowplan.com', '$2a$10$N.zmdr9k7uOCQb376NoUnuTJ8iAt6Z5EHsM8lE9P2kg9xjdNdCj0y', 'DEMANDER', 10, NULL, true),

-- 测试人员
('test_engineer', 'test@flowplan.com', '$2a$10$N.zmdr9k7uOCQb376NoUnuTJ8iAt6Z5EHsM8lE9P2kg9xjdNdCj0y', 'TESTER', 11, NULL, true),
('qa_lead', 'qalead@flowplan.com', '$2a$10$N.zmdr9k7uOCQb376NoUnuTJ8iAt6Z5EHsM8lE9P2kg9xjdNdCj0y', 'TESTER', 12, NULL, true),

-- DBA
('dba_admin', 'dba@flowplan.com', '$2a$10$N.zmdr9k7uOCQb376NoUnuTJ8iAt6Z5EHsM8lE9P2kg9xjdNdCj0y', 'DBA', 4, NULL, true);

-- 插入示例项目数据
INSERT INTO project (project_name, description, status, priority, creator_id, deadline) VALUES 
('FlowPlan需求管理平台', '开发一个全生命周期的需求管理平台，支持Excel需求上传、待办任务管理、AI智能辅助等功能', 'IN_PROGRESS', 'HIGH', 5, '2024-06-30 18:00:00'),
('用户权限管理系统', '设计和实现基于角色的权限管理系统，支持多级组织架构和细粒度权限控制', 'PLANNING', 'HIGH', 5, '2024-05-15 18:00:00'),
('移动端应用优化', '优化现有移动端应用的性能，提升用户体验，支持离线模式', 'TESTING', 'MEDIUM', 6, '2024-04-20 18:00:00'),
('数据可视化大屏', '开发实时数据监控大屏，展示关键业务指标和运营数据', 'PLANNING', 'MEDIUM', 5, '2024-07-10 18:00:00'),
('API网关升级', '升级现有API网关，增加限流、熔断、监控等功能', 'COMPLETED', 'LOW', 2, '2024-03-01 18:00:00');

-- 插入示例Excel计划数据
INSERT INTO excel_plan (project_id, file_name, file_path, file_size, sheet_names, demander, upload_user_id) VALUES 
(1, 'FlowPlan功能需求清单.xlsx', '/uploads/excel/flowplan_requirements_20240301.xlsx', 245760, '["功能清单", "用户故事", "接口设计"]', '产品部', 5),
(2, '权限管理需求说明.xlsx', '/uploads/excel/permission_requirements_20240302.xlsx', 189440, '["权限模型", "角色定义", "功能矩阵"]', '产品部', 5),
(3, '移动端优化需求.xlsx', '/uploads/excel/mobile_optimization_20240303.xlsx', 156672, '["性能优化", "UI改进", "功能增强"]', '用户体验部', 6);

-- 插入示例待办任务数据
INSERT INTO todo (project_id, title, description, status, priority, todo_type, assignee_id, reporter_id, due_date, estimated_hours) VALUES 
-- FlowPlan项目的待办任务
(1, '需求分析和整理', '分析产品需求文档，整理功能清单和用户故事', 'DONE', 'HIGH', 'COMMUNICATION', 5, 5, '2024-03-05 18:00:00', 16.0),
(1, '技术方案设计', '设计系统架构，选择技术栈，制定开发计划', 'DONE', 'HIGH', 'DOCUMENT', 2, 5, '2024-03-10 18:00:00', 24.0),
(1, '数据库设计', '设计数据库表结构，建立实体关系模型', 'DONE', 'HIGH', 'DATA_SOURCE', 9, 2, '2024-03-12 18:00:00', 12.0),
(1, '后端API开发', '开发Spring Boot后端服务，实现认证、项目管理、待办管理等API', 'IN_PROGRESS', 'HIGH', 'DEVELOP', 3, 2, '2024-03-25 18:00:00', 80.0),
(1, 'Flutter客户端开发', '开发Flutter移动端应用，实现用户界面和业务逻辑', 'IN_PROGRESS', 'HIGH', 'DEVELOP', 4, 2, '2024-03-30 18:00:00', 120.0),
(1, 'Excel处理功能', '实现Excel文件上传、解析和需求提取功能', 'TODO', 'HIGH', 'DEVELOP', 3, 5, '2024-03-28 18:00:00', 32.0),
(1, 'AI服务集成', '集成通义千问API，实现智能需求分析和文档生成', 'TODO', 'MEDIUM', 'DEVELOP', 3, 5, '2024-04-05 18:00:00', 24.0),
(1, '单元测试编写', '为后端服务编写单元测试，确保代码质量', 'TODO', 'MEDIUM', 'TEST', 7, 3, '2024-04-10 18:00:00', 40.0),
(1, '集成测试', '进行前后端集成测试，验证业务流程', 'TODO', 'HIGH', 'TEST', 8, 7, '2024-04-15 18:00:00', 32.0),
(1, 'UAT用户验收', '邀请用户进行验收测试，收集反馈意见', 'TODO', 'HIGH', 'UAT', 5, 5, '2024-04-20 18:00:00', 16.0),

-- 权限管理项目的待办任务
(2, '权限模型设计', '设计RBAC权限模型，定义用户、角色、权限关系', 'IN_PROGRESS', 'HIGH', 'DOCUMENT', 2, 5, '2024-03-20 18:00:00', 20.0),
(2, '组织架构设计', '设计多级组织架构，支持部门和岗位管理', 'TODO', 'HIGH', 'DOCUMENT', 2, 5, '2024-03-25 18:00:00', 16.0),
(2, '权限数据库设计', '设计权限相关的数据库表结构', 'TODO', 'HIGH', 'DATA_SOURCE', 9, 2, '2024-03-28 18:00:00', 12.0),

-- 移动端优化项目的待办任务
(3, '性能问题分析', '分析当前移动端应用的性能瓶颈', 'DONE', 'HIGH', 'COMMUNICATION', 4, 6, '2024-03-01 18:00:00', 8.0),
(3, '优化方案制定', '制定性能优化的具体方案和实施计划', 'DONE', 'HIGH', 'DOCUMENT', 4, 6, '2024-03-05 18:00:00', 12.0),
(3, '代码优化实施', '实施代码层面的性能优化', 'IN_PROGRESS', 'HIGH', 'DEVELOP', 4, 4, '2024-03-15 18:00:00', 40.0),
(3, '性能测试', '进行性能测试，验证优化效果', 'TODO', 'HIGH', 'TEST', 7, 4, '2024-03-18 18:00:00', 16.0);

-- 插入Excel与待办的关联关系
INSERT INTO excel_todo_rel (excel_id, todo_id, relation_type) VALUES 
(1, 1, 'DERIVED'),
(1, 2, 'DERIVED'),
(1, 6, 'DERIVED'),
(1, 7, 'DERIVED'),
(2, 11, 'DERIVED'),
(2, 12, 'DERIVED'),
(2, 13, 'DERIVED'),
(3, 14, 'DERIVED'),
(3, 15, 'DERIVED'),
(3, 16, 'DERIVED'),
(3, 17, 'DERIVED');

-- 插入示例笔记数据
INSERT INTO note (project_id, todo_id, title, content, note_type, tags, author_id, is_public) VALUES 
(1, 1, '需求分析会议纪要', '参会人员：产品经理、技术负责人、UI设计师\n会议内容：\n1. 确认核心功能范围\n2. 讨论技术实现方案\n3. 明确项目里程碑\n\n决议：\n- 采用Spring Boot + Flutter技术栈\n- 分阶段迭代开发\n- 每周进行进度review', 'MEETING', '需求分析,会议纪要,技术方案', 5, true),
(1, 4, '后端API设计规范', '# API设计规范\n\n## 1. 接口命名\n- 使用RESTful风格\n- 动词使用HTTP方法表示\n- 名词使用复数形式\n\n## 2. 响应格式\n```json\n{\n  "code": 200,\n  "message": "操作成功",\n  "data": {}\n}\n```\n\n## 3. 错误处理\n- 统一错误码定义\n- 提供清晰的错误信息\n- 记录详细的错误日志', 'SOLUTION', 'API设计,后端开发,规范', 2, true),
(1, 5, 'Flutter状态管理方案', '# Flutter状态管理选型\n\n经过调研和对比，决定使用Riverpod作为状态管理方案：\n\n## 优势：\n1. 类型安全\n2. 测试友好\n3. 编译时依赖检查\n4. 良好的性能表现\n\n## 实施计划：\n1. 搭建基础Provider结构\n2. 实现认证状态管理\n3. 完善业务状态管理', 'SOLUTION', 'Flutter,状态管理,Riverpod', 4, true),
(2, 11, '权限设计思路', '# RBAC权限模型设计\n\n## 核心概念：\n- User（用户）\n- Role（角色）\n- Permission（权限）\n- Resource（资源）\n\n## 设计原则：\n1. 最小权限原则\n2. 职责分离\n3. 权限继承\n4. 动态权限检查\n\n## 实现要点：\n- 支持多级组织架构\n- 权限可继承和覆盖\n- 支持动态权限分配', 'SOLUTION', '权限管理,RBAC,设计思路', 2, true);

-- 插入审计日志示例数据
INSERT INTO audit_log (operator_id, operation_type, resource_type, resource_id, old_value, new_value, ip_address, user_agent) VALUES 
(1, 'CREATE', 'PROJECT', 1, NULL, '{"name":"FlowPlan需求管理平台","status":"PLANNING"}', '192.168.1.100', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36'),
(5, 'UPDATE', 'PROJECT', 1, '{"status":"PLANNING"}', '{"status":"IN_PROGRESS"}', '192.168.1.101', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36'),
(2, 'CREATE', 'TODO', 1, NULL, '{"title":"需求分析和整理","status":"TODO"}', '192.168.1.102', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36'),
(5, 'UPDATE', 'TODO', 1, '{"status":"TODO"}', '{"status":"DONE"}', '192.168.1.101', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36'),
(5, 'CREATE', 'EXCEL_PLAN', 1, NULL, '{"fileName":"FlowPlan功能需求清单.xlsx"}', '192.168.1.101', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36');

-- 创建索引（如果complete_schema.sql中没有包含的话）
CREATE INDEX IF NOT EXISTS idx_project_deadline ON project(deadline) WHERE deadline IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_todo_due_date ON todo(due_date) WHERE due_date IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_audit_log_create_time_desc ON audit_log(create_time DESC);

-- 插入一些统计查询的视图（可选）
CREATE OR REPLACE VIEW project_stats AS
SELECT 
    p.id as project_id,
    p.project_name,
    p.status as project_status,
    COUNT(t.id) as total_todos,
    COUNT(CASE WHEN t.status = 'DONE' THEN 1 END) as completed_todos,
    COUNT(CASE WHEN t.status = 'IN_PROGRESS' THEN 1 END) as in_progress_todos,
    COUNT(CASE WHEN t.status = 'TODO' THEN 1 END) as pending_todos,
    COUNT(CASE WHEN t.status = 'BLOCKED' THEN 1 END) as blocked_todos,
    ROUND(
        CASE 
            WHEN COUNT(t.id) > 0 
            THEN (COUNT(CASE WHEN t.status = 'DONE' THEN 1 END) * 100.0 / COUNT(t.id))
            ELSE 0 
        END, 2
    ) as completion_percentage
FROM project p
LEFT JOIN todo t ON p.id = t.project_id
GROUP BY p.id, p.project_name, p.status;

-- 插入用户工作负载统计视图
CREATE OR REPLACE VIEW user_workload AS
SELECT 
    u.id as user_id,
    u.username,
    u.email,
    o.org_name,
    COUNT(t.id) as total_assigned_todos,
    COUNT(CASE WHEN t.status = 'TODO' THEN 1 END) as pending_todos,
    COUNT(CASE WHEN t.status = 'IN_PROGRESS' THEN 1 END) as active_todos,
    COUNT(CASE WHEN t.status = 'DONE' THEN 1 END) as completed_todos,
    SUM(t.estimated_hours) as total_estimated_hours,
    SUM(t.actual_hours) as total_actual_hours
FROM sys_user u
LEFT JOIN sys_org o ON u.org_id = o.id
LEFT JOIN todo t ON u.id = t.assignee_id
WHERE u.is_active = true
GROUP BY u.id, u.username, u.email, o.org_name;

-- 插入一些触发器来自动更新时间戳（如果数据库支持）
-- PostgreSQL示例：
-- CREATE OR REPLACE FUNCTION update_modified_column()
-- RETURNS TRIGGER AS $$
-- BEGIN
--     NEW.update_time = CURRENT_TIMESTAMP;
--     RETURN NEW;
-- END;
-- $$ language 'plpgsql';

-- CREATE TRIGGER update_sys_user_modtime BEFORE UPDATE ON sys_user FOR EACH ROW EXECUTE FUNCTION update_modified_column();
-- CREATE TRIGGER update_project_modtime BEFORE UPDATE ON project FOR EACH ROW EXECUTE FUNCTION update_modified_column();
-- CREATE TRIGGER update_todo_modtime BEFORE UPDATE ON todo FOR EACH ROW EXECUTE FUNCTION update_modified_column();

-- 提交数据
COMMIT;